﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Linq;
using System.Linq;

namespace Demo.Classroom
{
    public partial class DemoADO : Form
    {
        public DemoADO()
        {
            InitializeComponent();
        }

        private DataTable GetCategories()
        {
            // Load dữ liệu cho combobox, DB Northwin, Table Categories
            // dùng DataTable
            DataTable categoryTable = new DataTable();

            using (SqlConnection sqlConnection = new SqlConnection())
            {
                sqlConnection.ConnectionString =
                    "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";

                sqlConnection.Open();

                SqlCommand selectCommand = new SqlCommand("SELECT * FROM Categories", sqlConnection);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);

                sqlDataAdapter.Fill(categoryTable);
            }

            return categoryTable;
        }

        // Lấy Product theo ngôn ngữ LINQ to SQL
        private IEnumerable<Product> GetProductsByLINQ(int categoryId)
        {
            // Dùng LINQ to SQL
            DataContext dc = new DataContext(
                "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;");

            Table<Product> products = dc.GetTable<Product>();

            IEnumerable<Product> productByCategory =
                from p in products  // mệnh đề from luôn ở vị trí đầu tiên
                where p.CategoryId == categoryId    // where, orderby, groupby
                select p; // select luôn ở cuối cùng

            return productByCategory;
        }

        // Lấy Product theo categoryId
        private List<Product> GetProducts(int categoryId)
        {
            List<Product> productList = new List<Product>();
            
            // Làm sao lấy dữ liệu từ DB, table Product -> biến product
            // Dùng SqlDataReader
            using (SqlConnection sqlConnection = new SqlConnection())
            {
                sqlConnection.ConnectionString =
                    "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";

                sqlConnection.Open();

                SqlCommand selectCommand = new SqlCommand(
                    "SELECT ProductId, ProductName, UnitPrice FROM Products WHERE CategoryId = @CategoryId",
                    sqlConnection);

                SqlParameter parameter = new SqlParameter("@CategoryId", SqlDbType.Int);
                
                parameter.Value = categoryId;
                selectCommand.Parameters.Add(parameter);
                
                SqlDataReader sqlDataReader = selectCommand.ExecuteReader();

                // Đọc lên từng dòng, gán dữ liệu vô object product
                while(sqlDataReader.Read())
                {
                    Product product = new Product();

                    product.ProductId = sqlDataReader.GetInt32(0);
                    product.ProductName = sqlDataReader.GetString(1);
                    product.UnitPrice = sqlDataReader.GetDecimal(2);

                    productList.Add(product);
                }
            }

            return productList;
        }

        private void DemoADO_Load(object sender, EventArgs e)
        {
            categoryCombo.DisplayMember = "CategoryName";
            categoryCombo.ValueMember = "CategoryId";

            categoryCombo.DataSource = GetCategories();
            
            // Bắt sự kiện SelectedIndexChange cho combobox
            // Show message box hiện CategoryId của cái Item đang chọn
        }

        private void categoryCombo_SelectedIndexChanged(object sender, EventArgs e)
        {
            //dataGridView1.DataSource = GetProducts((int)categoryCombo.SelectedValue);
            dataGridView1.DataSource = GetProductsByLINQ((int)categoryCombo.SelectedValue);
        }
    }
}